package ca.uwo.android.rss.events;


import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class EventsDataSource {

  // Database fields
  private SQLiteDatabase database;
  private MySQLiteHelper dbHelper;
  
  private String[] all_FeedColumns = { MySQLiteHelper.COLUMN_ID, 
							           MySQLiteHelper.COLUMN_URL,
							           MySQLiteHelper.COLUMN_NAME,         
							           MySQLiteHelper.COLUMN_LAST_UPDATE };
  
  private String[] all_EntryColumns = { MySQLiteHelper.COLUMN_ID, 
			                            MySQLiteHelper.COLUMN_FEED_ID, 
			                            MySQLiteHelper.COLUMN_TITLE, 
			                            MySQLiteHelper.COLUMN_DESCRIPTION, 
			                            MySQLiteHelper.COLUMN_LINK,
			                            MySQLiteHelper.COLUMN_DATE };
  
  public EventsDataSource(Context context) {
    dbHelper = new MySQLiteHelper(context);
   // delete_tables();
   // create_tables();
  }
  
  public void create_tables() throws SQLException {
	  
	    final String TABLE_FEEDS    = "feeds";
	    final String TABLE_ENTRIES  = "entries";
	    
	    final String COLUMN_ID = "_id";
	    final String COLUMN_URL = "url";
	    final String COLUMN_NAME = "name";
	    final String COLUMN_LAST_UPDATE = "last_update";
	    
	    final String COLUMN_FEED_ID = "feed_id";
	    final String COLUMN_TITLE = "title";
	    final String COLUMN_DESCRIPTION = "description";
	    final String COLUMN_LINK = "link";
	    final String COLUMN_DATE = "date";
	    
	    // Database creation sql statement
	    final String CREATE_FEEDS_TABLE  = 
	  						      "create table " 			+ TABLE_FEEDS + "("
	  						      + COLUMN_ID     			+  " integer primary key autoincrement, " 
	  						      + COLUMN_URL  			+  " text not null, "
	  							  + COLUMN_NAME      		+  " text not null, "
	  							  + COLUMN_LAST_UPDATE      +  " text not null);";
	    
	    final String CREATE_ENTRIES_TABLE  = 
	  						      "create table " 			+ TABLE_ENTRIES + "("
	  						      + COLUMN_ID     			+  " integer primary key autoincrement, "						      
	  						      + COLUMN_FEED_ID 			+  " integer not null, "
	  						      + COLUMN_TITLE  			+  " text not null, "
	  							  + COLUMN_DESCRIPTION      +  " text not null, "
	  							  + COLUMN_LINK             +  " text not null, "
	    							  + COLUMN_DATE             +  " text not null,  "
	    							  + "FOREIGN KEY (" + COLUMN_FEED_ID + ") REFERENCES " + TABLE_FEEDS + " (" + COLUMN_ID + ") );";
	  
	    open_database();
	    database.execSQL(CREATE_FEEDS_TABLE);
	    database.execSQL(CREATE_ENTRIES_TABLE);  
  }
  
  public void delete_tables() throws SQLException {
	  
	    open_database();
	    database.execSQL("DROP TABLE IF EXISTS feeds");
	    database.execSQL("DROP TABLE IF EXISTS entries");
	    
	    Cursor cursor = database.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='table_name';", null);

	    cursor.moveToFirst();
	    
	    if(cursor.getCount() == 0){
	    	Log.i("BEN234", "NO ENTRIES TABLE");
	    }
	    
	    Log.i("BEN", "CURSOR COUNT is: " + cursor.getCount());
  }

  public void open_database() throws SQLException {
    database = dbHelper.getWritableDatabase();
  }

  public void close_helper() {
    dbHelper.close();
  }

  public void createFeed(String url, String name, String date) {
	    
	ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.COLUMN_URL, url);
    values.put(MySQLiteHelper.COLUMN_NAME, name);
    values.put(MySQLiteHelper.COLUMN_LAST_UPDATE, date);
    database.insert(MySQLiteHelper.TABLE_FEEDS, null, values);
  }
  
  public void createEvent(int feed_id, String title, String description, String link, String date) {
    
	ContentValues values = new ContentValues();
    values.put(MySQLiteHelper.COLUMN_FEED_ID, feed_id);
    values.put(MySQLiteHelper.COLUMN_TITLE, title);
    values.put(MySQLiteHelper.COLUMN_DESCRIPTION, description);
    values.put(MySQLiteHelper.COLUMN_LINK, link);
    values.put(MySQLiteHelper.COLUMN_DATE, date);
    database.insert(MySQLiteHelper.TABLE_ENTRIES, null, values);
  }
  

  /*
  public void deleteComment(RssEvent comment) {
    long id = comment.getId();
    System.out.println("Comment deleted with id: " + id);
    database.delete(MySQLiteHelper.TABLE_COMMENTS, MySQLiteHelper.COLUMN_ID
        + " = " + id, null);
  }
  */
  
  public List<RssEvent> getAllFeeds() {

	List<RssEvent> events = new ArrayList<RssEvent>();
    Cursor cursor = database.query(MySQLiteHelper.TABLE_FEEDS, all_FeedColumns, null, null, null, null, null);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      RssEvent event = cursorToEvent(cursor);
      events.add(event);
      cursor.moveToNext();
    }
    // Make sure to close the cursor
    cursor.close();
 
    return events;
  }
  
  public List<RssEvent> getAllEntries() {

	  
	List<RssEvent> events = new ArrayList<RssEvent>();
    Cursor cursor = database.query(MySQLiteHelper.TABLE_ENTRIES, all_EntryColumns, null, null, null, null, null);

    cursor.moveToFirst();
    while (!cursor.isAfterLast()) {
      RssEvent event = cursorToEvent(cursor);
      events.add(event);
      cursor.moveToNext();
    }
    // Make sure to close the cursor
    cursor.close();
 
    return events;
  }
  
  public boolean hasEntries(){
	  

	  
	    Cursor cursor = database.rawQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='entries';", null);
	    boolean result = true;
	    
	    Log.i("BEN", "CURSOR COUNT is: " + cursor.getCount());
	    
	    if(cursor.getCount() != 0){ // entries table exists
		    
	    	Cursor cursor2 = database.rawQuery("SELECT COUNT(*)FROM entries;", null);
		    cursor2.moveToFirst();
		    
		    Log.i("BEN", "CURSOR COUNT2 is: " + cursor2.getInt(0));
		    if(cursor2.getInt(0) == 0) // table exists, but no entries
		    	return false;
		    
		    cursor2.close();
	    }
	    else{ // entries table missing
	    	Log.i("BEN", "NO ENTRIES TABLE");
	    	result = false;
	    }

	    cursor.close();
	    Log.i("BEN", "RESULT: "+ result);
	    return result;
  }

  private RssEvent cursorToEvent(Cursor cursor) {
    
	RssEvent event = new RssEvent();
    event.setId(cursor.getInt(0));
    event.setFeed_id((cursor.getInt(1)));
    event.setTitle(cursor.getString(2));
    event.setDescription(cursor.getString(3));
    event.setLink(cursor.getString(4));
    event.setDate(cursor.getString(5));

    return event;
  }
  
} 